package com.sharein.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class Database {
	private Connection con;
	private DatabaseMetaData dbm;
	
	public Database() {
		DataSource ds = new DataSource();
		try {
			con = ds.getConnection();
			dbm = con.getMetaData();
		}
		catch(Exception e) {
			System.out.println("Error of DatabaseMetaData : " + e.getMessage());
		}
	}
	
	public void closeConnextion() {
		try {
			con.close();
			System.out.println("Connection finished.");
		} catch (SQLException e) {
			System.out.println("Error of closeConnextion : " + e.getMessage());
		}
	}
	
	public Vector<String> getTables() {
		Vector<String> v = new Vector<String>();
		try {
			ResultSet rs = dbm.getTables(null, null, null, new String[]{"TABLE", "VIEW"});
			while (rs.next()) {
				v.add(rs.getString(3));
			}
			return v;
		}
		catch(Exception e) {
			System.out.println("Error of getTables : " + e.getMessage());
			return null;
		}
	}

	public Object[][] select(String tableName, String fields[]) {
		StringBuffer query = new StringBuffer("");
		if(fields ==null) query.append("SELECT * FROM " + tableName);
		else {
			query.append("SELECT " + fields[0]);
			for (int i = 1; i<fields.length; i++) query.append(", " + fields[i]);
			query.append(" FROM " + tableName);
		}
		System.out.println(query);
		try {
			Statement sql = con.createStatement();
			ResultSet rs = sql.executeQuery(query.toString());
			ResultSetMetaData rsm = rs.getMetaData();
			int n = rsm.getColumnCount();
			rs.last();
			int l = rs.getRow();
			rs.beforeFirst();
			
			Object T[][];
			T = new Object[l][n];
			while (rs.next()) {
				for (int i = 1; i <= n; i++)
					T[rs.getRow()-1][i-1] = rs.getObject(i);
			}
			return T;
		}
		catch(Exception e) {
			System.out.println("Error of select : " + e.getMessage());
			return null;
		}
	}
	
	public Object[][] select(String tableName, String key, Object value, String fields[]) {
		StringBuffer query = new StringBuffer("");
		if(fields ==null) {
			if(value.getClass() == String.class) query.append("SELECT * FROM " + tableName + " WHERE " + key + " LIKE '" + value + "'");
			else query.append("SELECT * FROM " + tableName + " WHERE " + key + " LIKE " + value);
		}
		else {
			query.append("SELECT " + fields[0]);
			for (int i = 1; i<fields.length; i++) query.append(", " + fields[i]);
			if(value.getClass() == String.class) query.append(" FROM " + tableName + " WHERE " + key + " LIKE '" + value + "'");
			else query.append(" FROM " + tableName + " WHERE " + key + " LIKE " + value);
		}
		System.out.println(query);
		try {
			Statement sql = con.createStatement();
			ResultSet rs = sql.executeQuery(query.toString());
			ResultSetMetaData rsm = rs.getMetaData();
			int n = rsm.getColumnCount();
			rs.last();
			int l = rs.getRow();
			rs.beforeFirst();
			
			Object T[][];
			T = new Object[l][n];
			while (rs.next()) {
				for (int i = 1; i <= n; i++)
					T[rs.getRow()-1][i-1] = rs.getObject(i);
					
			}
			return T;
		}
		catch(Exception e) {
			System.out.println("Error of select : " + e.getMessage());
			return null;
		}
	}
	
	public int insert(String tableName, Object values[], String fields[]) {
		StringBuffer query = new StringBuffer("");
		if(fields ==null) {
			if(values[0].getClass() == String.class) query.append("INSERT INTO " + tableName + " VALUES('" + values[0] + "'");
			else query.append("INSERT INTO " + tableName + " VALUES(" + values[0]);
		}
		else {
			query.append("INSERT INTO " 
					+ tableName + "(" + fields[0]);
			for(int i=1; i<fields.length; i++) query.append(", " + fields[i]);
			query.append(")");
			if(values[0].getClass() == String.class) query.append(" VALUES('" + values[0] + "'");
			else query.append(" VALUES(" + values[0]);
		}
		
		for(int i=1; i<values.length; i++) {
			if(values[i].getClass() == String.class) query.append(", '" + values[i] + "'");
			else query.append(", " + values[i]);
		}
		query.append(")");
		System.out.println(query);
		try {
			Statement sql = con.createStatement();
			return sql.executeUpdate(query.toString());
		}
		catch(Exception e) {
			System.out.println("Error of insert : " + e.getMessage());
			return -1;
		}
	}
	
	public int update(String tablename, String columns[], Object[] columnsValues, String key, Object keyValue) {
		StringBuffer query;
		if(columnsValues[0].getClass() == String.class) query = new StringBuffer("UPDATE " + tablename + " SET " + columns[0] + "='" + columnsValues[0] + "'");
		else query = new StringBuffer("UPDATE " + tablename + " SET " + columns[0] + "=" + columnsValues[0]);
		if(key == null && columns.length > 0) {
			for(int i=1; i<columns.length; i++) {
				if(columnsValues[i].getClass() == String.class) query.append(", " + columns[i] + "='" + columnsValues[i] + "'");
				else query.append(", " + columns[i] + "=" + columnsValues[i]);
			}
		}
		else {
			for(int i=1; i<columns.length; i++) {
				if(columnsValues[i].getClass() == String.class) query.append(", " + columns[i] + "='" + columnsValues[i] + "'");
				else query.append(", " + columns[i] + "=" + columnsValues[i]);
			}
			if(keyValue.getClass() == String.class) query.append(" WHERE " + key + " LIKE '" + keyValue + "'");
			else query.append(" WHERE " + key + " LIKE " + keyValue);
		}
		System.out.println(query);
		try {
			Statement sql = con.createStatement();
			return sql.executeUpdate(query.toString());
		}
		catch(Exception e) {
			System.out.println("Error of insert : " + e.getMessage());
			return -1;
		}
	}
	
	public int delete(String tableName, String key, Object value) {
		StringBuffer query = new StringBuffer("DELETE FROM " + tableName);
		if(key != null) {
			if(value.getClass() == String.class) query.append(" WHERE " + key + " LIKE '" + value + "'");
			else query.append(" WHERE " + key + " LIKE " + value);
		}
		System.out.println(query);
		try {
			Statement sql = con.createStatement();
			return sql.executeUpdate(query.toString());
		}
		catch(Exception e) {
			System.out.println("Error of insert : " + e.getMessage());
			return -1;
		}
	}
	
}
